﻿using System;
using Microsoft.Data.SqlClient;

namespace WebAPI.Utils
{
    class Database
    {
        private string connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase; User ID=myUsername;Password=myPassword;";
        
        // 测试连接数
        public static void ConnectionState()
        {
            for (int i = 0; i < 1000; i++)
            {
                var j = i + 1;
                try
                {
                    var connectStr = "server=10.10.30.11;uid=sa;pwd=sa@123;database=shikongdb_TianLaLa;Pooling=true;Max Pool Size=512;";
                    var conn = new SqlConnection(connectStr);
                    Console.WriteLine($"第{j}连接尝试打开");
                    conn.Open();
                    Console.WriteLine($"第{j}连接已打开");
                }
                catch (Exception e)
                {
                    Console.WriteLine($"第{j}个异常：{e.Message}");
                }
            }
        }
        
        //连接数据库
        void Connect()
        {
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                //打开数据库连接
                connection.Open();

                //执行数据库操作
                //...

            }
            catch (Exception ex)
            {
                //处理异常
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //关闭数据库连接
                connection.Close();
            }
        }

        //查询数据
        void Select()
        {
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                //打开数据库连接
                connection.Open();

                //定义SQL语句
                string sql = "SELECT * FROM Students;";

                //创建SqlCommand对象
                SqlCommand command = new SqlCommand(sql, connection);

                //执行SQL语句
                SqlDataReader dataReader = command.ExecuteReader();//返回一个或多个结果集

                // 单行
                //if (dataReader.Read()) {
                //    dataReader["Name"];
                //}

                //遍历查询结果
                while (dataReader.Read())
                {
                    Console.WriteLine("{0}\t{1}\t{2}", dataReader["Name"], dataReader["Age"], dataReader["Gender"]);
                }

                //判断是否有下一个结果集
                if (dataReader.NextResult())
                {
                    while (dataReader.Read())
                    {
                        Console.WriteLine("{0}\t{1}\t{2}", dataReader["Name"], dataReader["Age"], dataReader["Gender"]);
                    }
                }

                //关闭DataReader
                dataReader.Close();
            }
            catch (Exception ex)
            {
                //处理异常
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //关闭数据库连接
                connection.Close();
            }
        }

        //查询数据
        void Find()
        {
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                //打开数据库连接
                connection.Open();

                //定义SQL语句
                string sql = "SELECT COUNT(*) FROM Students;";

                //创建SqlCommand对象
                SqlCommand command = new SqlCommand(sql, connection);

                //执行SQL语句
                var data = command.ExecuteScalar();//返回单列
                //遍历查询结果

                Console.WriteLine(data);
            }
            catch (Exception ex)
            {
                //处理异常
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //关闭数据库连接
                connection.Close();
            }
        }

        //增加数据
        void Add()
        {
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                //打开数据库连接
                connection.Open();

                //定义SQL语句


                string sql = "INSERT INTO Students (Name, Age, Gender) VALUES ('{0}', {1}, '{2}');";
                sql = string.Format(sql, "test", 18, "男");

                //创建SqlCommand对象
                SqlCommand command = new SqlCommand(sql, connection);

                //执行SQL语句
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine("Rows Affected: " + rowsAffected);
            }
            catch (Exception ex)
            {
                //处理异常
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //关闭数据库连接
                connection.Close();
            }
        }

        //更新数据
        void Update()
        {
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                //打开数据库连接
                connection.Open();

                //定义SQL语句
                string sql = "UPDATE Students SET Age = 21 WHERE Name = 'John';";

                //创建SqlCommand对象
                SqlCommand command = new SqlCommand(sql, connection);

                //执行SQL语句
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine("Rows Affected: " + rowsAffected);
            }
            catch (Exception ex)
            {
                //处理异常
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //关闭数据库连接
                connection.Close();
            }
        }

        //删除数据
        void Delete()
        {
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                //打开数据库连接
                connection.Open();

                //定义SQL语句
                string sql = "DELETE FROM Students WHERE Name = 'John';";

                //创建SqlCommand对象
                SqlCommand command = new SqlCommand(sql, connection);

                //执行SQL语句
                int rowsAffected = command.ExecuteNonQuery();// insert\update\delete
                Console.WriteLine("Rows Affected: " + rowsAffected);
            }
            catch (Exception ex)
            {
                //处理异常
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //关闭数据库连接
                connection.Close();
            }
        }

        // 事务
        void Transaction()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Start a local transaction.
                SqlTransaction sqlTran = connection.BeginTransaction();

                // Enlist a command in the current transaction.
                SqlCommand command = connection.CreateCommand();
                command.Transaction = sqlTran;

                try
                {
                    // Execute two separate commands.
                    command.CommandText =
                      "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')";
                    command.ExecuteNonQuery();
                    command.CommandText =
                      "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')";
                    command.ExecuteNonQuery();

                    // Commit the transaction.
                    sqlTran.Commit();
                    Console.WriteLine("Both records were written to database.");
                }
                catch (Exception ex)
                {
                    // Handle the exception if the transaction fails to commit.
                    Console.WriteLine(ex.Message);

                    try
                    {
                        // Attempt to roll back the transaction.
                        sqlTran.Rollback();
                    }
                    catch (Exception exRollback)
                    {
                        // Throws an InvalidOperationException if the connection
                        // is closed or the transaction has already been rolled
                        // back on the server.
                        Console.WriteLine(exRollback.Message);
                    }
                }
            }
        }
    }
}
